import time
from HLog import HLog
import configparser
import os
import shutil
import csv
from dataAnlysis import headMain
from package import const, twanalysis, thanalysis
from package.MysqlHelp import _MySqlHelper
import datetime
import zipfile
import gol
import decimal

mysql = _MySqlHelper()
dictc = {}


# 清空表数据
def removeDataBase(name):
    global mysql
    gol.set_value('dbName', name)
    sql = "select CONCAT('TRUNCATE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,';') from information_schema" \
          ".tables " \
          "where TABLE_SCHEMA in ('" + name + "');"
    listSql = mysql.select(sql)

    inSqlList = []
    for t in listSql:
        for sqlTmp in t:
            if (sqlTmp.find("fare_statistics") >= 0) or (sqlTmp.find("big_data_statistics") >= 0):
                pass
            else:
                inSqlList.append(sqlTmp)

    mysql.executeAll(inSqlList)


def removeLastDayFromSql():
    removeDataBase("transaction_datalc")
    removeDataBase("transaction_datasc")


def del_file(path):
    ls = os.listdir(path)
    for i in ls:
        c_path = os.path.join(path, i)
        if os.path.isdir(c_path):
            del_file(c_path)
        else:
            os.remove(c_path)


def add_1(binary_inpute):  # 二进制编码加1
    _, out = bin(int(binary_inpute, 2) + 1).split("b")
    return out


def reverse(binary_inpute):  # 取反操作
    binary_inpute = list(binary_inpute)
    binary_out = binary_inpute
    for epoch, i in enumerate(binary_out):
        if i == "0":
            binary_out[epoch] = "1"
        else:
            binary_out[epoch] = "0"
    return "".join(binary_out)


class DownLoadTools(object):

    def __init__(self):
        rq = time.strftime('%Y%m%d', time.localtime(time.time()))
        self.log = HLog("main", rq)
        self.log.info('开始运行.......')
        self.currentPath = "./alysisFile/" + str(datetime.datetime.now().weekday()) + "day"
        gol._init()
        self.cf = configparser.ConfigParser()
        self.cf.read("./conf/deal.conf")

        self.bookdate = self.cf.get('option', 'bookdate')
        if self.bookdate == "":
            yesterday = datetime.datetime.today() + datetime.timedelta(-1)
            yesterday_format = yesterday.strftime('%Y%m%d')
            self.bookdate = yesterday_format

    def anlysisData(self):

        ftpList: list = self.cf.options("source_file")
        for key in ftpList:
            self.onecAnysis(key)

    def onecAnysis(self, key):
        tmpDir = self.cf.get("option", "tmpDir")
        sourceRoot = self.cf.get("option", "source_file")
        cfgStr = self.cf.get("source_file", key)
        attributes = cfgStr.split(',')
        sCurrentPath: str = sourceRoot + attributes[0]
        # 解压源文件zip
        for root, ds, fs in os.walk(sCurrentPath):
            if root == sCurrentPath:
                for f in fs:
                    if f.find(".zip") >= 0:
                        # if f.find(self.bookdate):
                        self.log.info(f)
                        zipf = None
                        try:
                            zipf = zipfile.ZipFile(root + f, 'r')
                        except Exception as e:

                            isExists = os.path.exists(tmpDir + self.bookdate)
                            if not isExists:
                                os.makedirs(tmpDir + self.bookdate)
                            shutil.copy(root + f, tmpDir + self.bookdate)
                            os.remove(root + f)

                            self.log.error("error" + f + str(e))
                        if zipf is None:
                            self.log.error(f)
                            continue
                        for file in zipf.namelist():
                            try:
                                zipf.extract(file, sCurrentPath)
                            except Exception as e:
                                self.log.error("erro" + f + str(e))
                        zipf.close()

                        isExists = os.path.exists(tmpDir + self.bookdate)
                        if not isExists:
                            os.makedirs(tmpDir + self.bookdate)
                        shutil.copy(root + f, tmpDir + self.bookdate)
                        os.remove(root + f)

        aCurrentPath = self.currentPath + "/" + attributes[0]
        # 清空解析后csv并创建
        shutil.rmtree(aCurrentPath)
        os.mkdir(aCurrentPath)

        dbName = ""
        if attributes[3] == "sc":
            gol.set_value('dbName', "transaction_datasc")
            dbName = "transaction_datasc"
        elif attributes[3] == "lc":
            gol.set_value('dbName', "transaction_datalc")
            dbName = "transaction_datalc"

        self.log.info("处理" + sCurrentPath)
        if os.path.exists(sCurrentPath):
            eval(attributes[5])(sCurrentPath, aCurrentPath, attributes[4], attributes[3], dbName)

    def statisticsToSql(self):
        global mysql
        with open('conf/sclc.csv', 'r', encoding='UTF-8') as f:
            reader = csv.reader(f)
            count = 0
            for row in reader:

                if count < 1:
                    count = count + 1
                    continue

                columns = ""
                columnsList = []
                for index in range(len(row)):
                    if index == 0:
                        continue
                    column = row[index]
                    if column is not "" and index < (len(row) - 2):
                        columns = columns + column + ","
                        columnsList.append(index)

                if row[len(row) - 2] is "":
                    cardid = "*"
                else:
                    cardid = row[len(row) - 2]
                columnsList.append(13)

                if row[len(row) - 1] is "":
                    txnamt = " "
                else:
                    txnamt = ",SUM(case when %s > 4000000000 then  - (%s^4294967295+ 1) " \
                             "ELSE %s END) AS txnamtall" % (row[len(row) - 1], row[len(row) - 1], row[len(row) - 1])
                    columnsList.append(14)

                # between = self.cf.get('mysql', 'statistics_between')
                # if between == "":
                #     yesterday = datetime.today() + timedelta(-1)
                #     yesterday_format = yesterday.strftime('%Y%m%d')
                #     between = row[1] + " BETWEEN " + yesterday_format + " AND " + yesterday_format + " "
                # else:
                #     between = row[1] + " " + between

                if row[0] == "txnproductpursecompensationfare":
                    print(1)

                sql = "SELECT " + columns + "COUNT(" + cardid + ") AS carnum" + txnamt + " FROM " + \
                      row[0] + " GROUP BY " + columns[:-1]
                re = mysql.select(sql)

                sqlColumn = ["bookdate", "occurrence_date", "lineid", "station_number", "paymethod", "equipmenttype",
                             "station_equipment_number", "posoperid", "udtype", "udsubtype", "producttype",
                             "crdmKnd", "crdSKnd", "carnum", "txnamtall"]
                columnStr = ""
                temIndex = 0
                for sIndex, columnIndex in enumerate(columnsList):
                    columnStr = columnStr + sqlColumn[columnIndex] + ","
                    if sqlColumn[columnIndex] == "station_equipment_number":
                        temIndex = sIndex

                sqllist = []

                for once in re:
                    vStr = ""
                    for ssindex, v in enumerate(once):
                        # if ssindex == (len(once) - 1):
                        # print(v)

                        # if len(txnamt) > 5 and ssindex == (len(once) - 1) and int(v) > 4000000000:
                        #     print(v)
                        #     a = int(v)
                        #     b = str(bin(a)).lstrip("0b")
                        #     b = reverse(b)
                        #     b = add_1(b)
                        #     b = -int(b, 2)
                        #
                        #     vStr = vStr + "\"" + str(b) + "\","
                        #     continue
                        if ssindex == temIndex and int(v) < 10:
                            vStr = vStr + "\"0" + str(v) + "\","
                        else:
                            vStr = vStr + "\"" + str(v) + "\","
                    sql = "INSERT INTO big_data_statistics ( bookdate," + columnStr[
                                                                          :-1] + ") VALUES (" + self.bookdate + "," + vStr[
                                                                                                                      :-1] + ")"
                    sqllist.append(sql)
                mysql.executeAll(sqllist)
                count = count + 1

    def finalStatistics(self, name):
        global mysql
        sql = "INSERT INTO fare_tmp (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall) " \
              "SELECT bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,SUM(carnum),0,0,0,SUM(txnamtall),0,0,0 " \
              "FROM big_data_statistics WHERE ((udtype = 8 AND udsubtype =15 ) OR (udtype = 3 AND udsubtype = 3 ) OR (udtype = 3 AND udsubtype = 2 ) OR (udtype = 3 AND udsubtype = 1 ) OR (udtype = 3 AND udsubtype = 121 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd"
        mysql.executeAll([sql])

        sql = "INSERT INTO fare_tmp (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall) " \
              "SELECT bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,0,0,SUM(carnum),0,0,SUM(txnamtall),0,0 " \
              "FROM big_data_statistics WHERE ((udtype = 8 AND udsubtype =14 ) OR (udtype = 3 AND udsubtype = 12 ) OR (udtype = 3 AND udsubtype = 11 ) OR (udtype = 3 AND udsubtype = 10 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd"
        mysql.executeAll([sql])

        sql = "INSERT INTO fare_tmp (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall) " \
              "SELECT bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,0,0,0,SUM(carnum),0,0,SUM(txnamtall),0 " \
              "FROM big_data_statistics WHERE ((udtype = 33 AND udsubtype =17 ) OR (udtype = 34 AND udsubtype = 17 ) OR (udtype = 35 AND udsubtype = 17 ) OR (udtype = 3 AND udsubtype = 120 ) OR (udtype = 3 AND udsubtype = 119 ) OR (udtype = 3 AND udsubtype = 118 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd "
        mysql.executeAll([sql])

        sql = "INSERT INTO fare_tmp (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall) " \
              "SELECT bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,0,SUM(carnum),0,0,0,0,0,SUM(txnamtall) " \
              "FROM big_data_statistics WHERE ((udtype = 3 AND udsubtype =28 ) OR (udtype = 3 AND udsubtype = 27 ) OR (udtype = 3 AND udsubtype = 26 ) OR (udtype = 1 AND udsubtype = 80 ) OR (udtype = 1 AND udsubtype = 81 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd "
        mysql.executeAll([sql])

        sql = "INSERT INTO subway." + name + " (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall)" \
                                             "SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),LPAD(trim(replace(station_number,'\t','')),2,0),paymethod,equipmenttype,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall) " \
                                             "FROM fare_tmp where bookdate = %s and (lineid = 2 or lineid = 3) " \
                                             "GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)

        mysql.executeAll([sql])

        sql = "INSERT INTO subway." + name + " (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall)" \
                                             "SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),LPAD(trim(replace(station_number,'\t','')),2,0),paymethod,equipmenttype,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number-60,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number-90,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall) " \
                                             "FROM fare_tmp where bookdate = %s and (lineid = 1 or lineid = 4)" \
                                             "GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)
        mysql.executeAll([sql])

    def sc_out_statistics(self):

        sql = "INSERT INTO subway.sc_out_statistics (bookdate,lineid,station_number,productType,productSubType,outCount,amount) " \
              "SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),LPAD(trim(replace(station_number,'\t','')),2,0),crdmKnd,crdSKnd,SUM(carnum),SUM(txnamtall) " \
              "FROM transaction_datasc.big_data_statistics WHERE ((udtype = 33 AND udsubtype = 13 ) " \
              " OR (udtype = 33 AND udsubtype = 15 ) OR (udtype = 34 AND udsubtype = 13 ) " \
              " OR (udtype = 34 AND udsubtype = 15 ) OR (udtype = 35 AND udsubtype = 13 ) " \
              " OR (udtype = 35 AND udsubtype = 15 ) OR (udtype = 3 AND udsubtype = 93 ) " \
              "OR (udtype = 3 AND udsubtype = 92 ) " \
              "OR (udtype = 3 AND udsubtype = 91 ) OR (udtype = 35 AND udsubtype = 113 ) " \
              "OR (udtype = 3 AND udsubtype = 115 ) OR (udtype = 35 AND udsubtype = 117 )) AND " \
              "bookdate = %s GROUP BY bookdate,lineid,station_number,crdmKnd,crdSKnd" % self.bookdate

        mysql.executeAll([sql])

    def update_day_cell(self):
        sql = "update subway.sw_st_stock AS ss " \
              "set ss. AGM_RECOVERY = (select  sum(ifnull(outCount,0)) FROM  subway.sc_out_statistics where bookdate='%s'  and lineid = ss.LINE_CODE and station_number= ss.STATION_CODE and (producttype = '3' or producttype = '1' or producttype = '12' ))," \
              "ss.BOX_OFFICE_SALE = (select  sum(ifnull(sale_carnum,0)) from  subway.sc_fare_statistics where bookdate='%s'  and lineid=ss.LINE_CODE and station_number= ss.STATION_CODE and (producttype = 3 or producttype =1 or producttype = 12 )  and equipmenttype=2)," \
              "ss.TVM_SELL_OUT = (select  sum(ifnull(sale_carnum,0))  from  subway.sc_fare_statistics where bookdate='%s'  and lineid=ss.LINE_CODE and station_number=ss.STATION_CODE and producttype=3  and equipmenttype=1) " \
              "where STATISTICS_DATE='%s' and del_flag=0 and TICKET_TYPE_PARAM_EN='PTDCP'" %(self.bookdate, self.bookdate, self.bookdate, self.bookdate)

        mysql.executeAll([sql])

    def updataToFinal(self):
        global mysql
        gol.set_value('dbName', "subway")

        # 钱箱入账
        self.box5109ToSubway()
        self.log.info("钱箱入账")

        # sw_au_tvm_amount_defference
        self.updateFinalForType231("sc", "2")
        self.updateFinalForType231("lc", "3")
        self.updateFinalForType231("acc", "4")
        self.update5109For231()

        # sw_au_tvm_box_amount_difference
        self.updateFinalForType232()

        # SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE
        self.updateFinalForType2314("sc", "1")
        self.updateFinalForType2314("lc", "2")
        self.updateFinalForType2314("acc", "3")

        # sw_au_conductor_hierarchy_amount_difference
        self.updateFinalForType2315("sc_fare_statistics", "lc_fare_statistics", "1")
        self.updateFinalForType2315("lc_fare_statistics", "acc_fare_statistics", "2")

        # SW_AU_ITVM_ONLINE_AMOUNT_HIERARCHY_DIFFERENCE
        self.updateFinalForType239("sc", 1, 2)
        self.updateFinalForType239("lc", 1, 3)
        self.updateFinalForType239("acc", 1, 4)

    def updateFinalForType231(self, typeName, typeID):

        sqlList = []
        # 叠加
        # 现金差异
        sql = "SELECT bookdate,lineid,station_number,equipmenttype,station_equipment_number,posoperid,SUM(sale_carnum+supplement_carnum),SUM(sale_txnamtall + supplement_txnamtall),SUM(recharge_carnum),SUM(recharge_txnamtall),SUM(refund_carnum),SUM(refund_txnamtall) " \
              "FROM %s_fare_statistics  " \
              "WHERE equipmenttype = 1 AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s " \
              "GROUP BY  bookdate,lineid,station_number,equipmenttype,station_equipment_number,posoperid" % (
                  typeName, self.bookdate)
        fc = mysql.select(sql)

        if fc is None:
            return
        for data in fc:
            sql = "UPDATE sw_au_tvm_amount_defference	" \
                  "SET  %s_CASH_SELL_TICKET = ifnull(%s_CASH_SELL_TICKET,0)  + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s'  AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" \
                  % (typeName, typeName, data[7], data[1], data[2], data[4], data[0])
            sqlList.append(sql)
            sql = "UPDATE sw_au_tvm_amount_defference	" \
                  "SET  %s_CASH_RECHARGE = ifnull(%s_CASH_RECHARGE,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" \
                  % (typeName, typeName, data[9], data[1], data[2], data[4], data[0])
            sqlList.append(sql)
            sql = "UPDATE sw_au_tvm_amount_defference	" \
                  "SET  %s_CASH_RETURN = ifnull(%s_CASH_RETURN,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" \
                  % (typeName, typeName, data[11], data[1], data[2], data[4], data[0])
            sqlList.append(sql)

            sql = "UPDATE sw_au_tvm_amount_defference	" \
                  "SET   DIFF_MONEY = ACTUAL_MONEY - (ifnull(%s_CASH_SELL_TICKET,0) + ifnull(%s_CASH_RECHARGE,0) + ifnull(%s_CASH_RETURN,0))," \
                  "SYSTEM_MONEY = (ifnull(%s_CASH_SELL_TICKET,0) +ifnull(%s_CASH_RECHARGE,0)-ifnull(%s_CASH_RETURN,0))" \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND  DATA_SOURCE = %s AND AUDIT_STATUS = 0" \
                  % (typeName, typeName, typeName, typeName, typeName, typeName, data[1], data[2], data[4], data[0],
                     typeID)
            sqlList.append(sql)

        # 电子差异
        sql = "SELECT bookdate,lineid,station_number,equipmenttype,station_equipment_number,posoperid,SUM(sale_carnum+supplement_carnum),SUM(sale_txnamtall + supplement_txnamtall),SUM(recharge_carnum),SUM(recharge_txnamtall),SUM(refund_carnum),SUM(refund_txnamtall) " \
              "FROM %s_fare_statistics  " \
              "WHERE equipmenttype = 1 AND (paymethod = 12 OR paymethod = 13 OR paymethod = 14 OR paymethod = 15) AND bookdate = %s " \
              "GROUP BY  bookdate,lineid,station_number,equipmenttype,station_equipment_number,posoperid" % (
                  typeName, self.bookdate)
        fc = mysql.select(sql)

        if fc is None:
            return
        for data in fc:
            sql = "UPDATE sw_au_tvm_amount_defference	" \
                  "SET  %s_ELEC_SELL_TICKET = ifnull(%s_ELEC_SELL_TICKET,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = \'%s\' AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" \
                  % (typeName, typeName, data[7], data[1], data[2], data[4], data[0])
            sqlList.append(sql)
            sql = "UPDATE sw_au_tvm_amount_defference	" \
                  "SET  %s_ELEC_RECHARGE = ifnull(%s_ELEC_RECHARGE,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = \'%s\' AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" \
                  % (typeName, typeName, data[9], data[1], data[2], data[4], data[0])
            sqlList.append(sql)
            sql = "UPDATE sw_au_tvm_amount_defference	" \
                  "SET  %s_ELEC_RETURN = ifnull(%s_ELEC_RETURN,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = \'%s\' AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" \
                  % (typeName, typeName, data[11], data[1], data[2], data[4], data[0])
            sqlList.append(sql)

            sql = "UPDATE sw_au_tvm_amount_defference	" \
                  "SET   DIFF_MONEY = ACTUAL_MONEY - (ifnull(%s_ELEC_SELL_TICKET,0) + ifnull(%s_ELEC_RECHARGE,0) - ifnull(%s_ELEC_RETURN,0))," \
                  "SYSTEM_MONEY = ifnull(%s_ELEC_SELL_TICKET,0) +ifnull(%s_ELEC_RECHARGE,0)-ifnull(%s_ELEC_RETURN,0)" \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND  DATA_SOURCE = %s AND AUDIT_STATUS = 0" \
                  % (
                      typeName, typeName, typeName, typeName, typeName, typeName, data[1], data[2], data[4], data[0],
                      typeID)
            sqlList.append(sql)

        mysql.executeAll(sqlList)

    def update5109For231(self):
        sqlList = []
        sql = "SELECT bookdate,lineid,station_number,station_equipment_number,SUM(amount) " \
              "FROM sc_tvm_coin_opt " \
              " WHERE bookdate = %s " \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number" % (self.bookdate)
        fc = mysql.select(sql)

        if fc is None:
            return
        for data in fc:
            sql = "UPDATE sw_au_tvm_amount_defference	" \
                  "SET   ACTUAL_MONEY =  ifnull(ACTUAL_MONEY,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = \'%s\'  AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND  DATA_SOURCE = 'sc' AND AUDIT_STATUS = 0" \
                  % (decimal.Decimal(data[4]), data[1], data[2], data[3], data[0])
            sqlList.append(sql)
        mysql.executeAll(sqlList)

    def updateFinalForType232(self):

        sqlList = []

        sql = "SELECT bookdate,lineid,station_number,station_equipment_number,coin_number,opt_type,amount " \
              " FROM subway.sc_tvm_coin_opt " \
              " WHERE bookdate = %s " % self.bookdate

        fc = mysql.select(sql)
        if fc is None:
            return
        for data in fc:

            if int(data[5]) == 2:
                sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
                      "set SC_UNLOAD_AMOUNT =  ifnull(SC_UNLOAD_AMOUNT,0) + %d " \
                      " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_CODE = '%s' " \
                      "AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" \
                      % (decimal.Decimal(data[6]), data[1], data[2], data[3], data[4], data[0])
                sqlList.append(sql)
            elif int(data[5]) == 1:
                sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
                      "set SC_SUPPLEMNT_AMOUNT =  ifnull(SC_SUPPLEMNT_AMOUNT,0) + %d" \
                      " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_CODE = '%s' " \
                      "AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" % (
                          decimal.Decimal(data[6]), data[1], data[2], data[3], data[4], data[0])
                sqlList.append(sql)

            # sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
            #       " SET SC_SALE_AMOUNT =  case when CASH_BOX_TYPE = 2 or CASH_BOX_TYPE = 4 then ifnull(SC_SUPPLEMNT_AMOUNT,0) - ifnull(SC_UNLOAD_AMOUNT,0) - ifnull(SC_HAND_OUT_AMOUNT,0)" \
            #       " when CASH_BOX_TYPE = 1 or CASH_BOX_TYPE = 3  then  ifnull(SC_UNLOAD_AMOUNT,0) - ifnull(SC_SUPPLEMNT_AMOUNT,0) + ifnull(SC_HAND_OUT_AMOUNT,0) END " \
            #       " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_CODE = '%s' " \
            #       "AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" % (
            #            data[1], data[2], data[3], data[4], data[0])
            # sqlList.append(sql)
            #
            # sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
            #       "SET SYSTEM_MONEY =  case when CASH_BOX_TYPE = 2 or CASH_BOX_TYPE = 4 then ifnull(SC_SUPPLEMNT_AMOUNT,0) - ifnull(SC_UNLOAD_AMOUNT,0) - ifnull(SC_HAND_OUT_AMOUNT,0)" \
            #       " when CASH_BOX_TYPE = 1 or CASH_BOX_TYPE = 3  then  ifnull(SC_UNLOAD_AMOUNT,0) - ifnull(SC_SUPPLEMNT_AMOUNT,0) + ifnull(SC_HAND_OUT_AMOUNT,0) END " \
            #       " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_CODE = '%s' AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND  DATA_SOURCE = 2 AND AUDIT_STATUS = 0" \
            #       % (data[1], data[2], data[3], data[4], data[0])
            # sqlList.append(sql)
            #
            # sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
            #       "SET " \
            #       " DIFF_MONEY =  case when CASH_BOX_TYPE = 2 or CASH_BOX_TYPE = 4 then ifnull(SUPPLEMENT_MONEY,0) - ifnull(ACTUAL_MONEY,0) - ifnull(SYSTEM_MONEY,0)" \
            #       " when CASH_BOX_TYPE = 1 or CASH_BOX_TYPE = 3  then  ifnull(ACTUAL_MONEY,0) - ifnull(SUPPLEMENT_MONEY,0) + ifnull(HAND_OUT_MONEY,0) - ifnull(SYSTEM_MONEY,0) END " \
            #       " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_CODE = '%s' AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND  DATA_SOURCE = 2 AND AUDIT_STATUS = 0" \
            #       % (data[1], data[2], data[3], data[4], data[0])
            # sqlList.append(sql)

            sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
                  " SET SC_SALE_AMOUNT =  case when CASH_BOX_TYPE = 2 or CASH_BOX_TYPE = 4 then ifnull(SC_SUPPLEMNT_AMOUNT,0) - ifnull(SC_UNLOAD_AMOUNT,0) - ifnull(SC_HAND_OUT_AMOUNT,0)" \
                  " when CASH_BOX_TYPE = 1 or CASH_BOX_TYPE = 3  then  ifnull(SC_UNLOAD_AMOUNT,0) - ifnull(SC_SUPPLEMNT_AMOUNT,0) + ifnull(SC_HAND_OUT_AMOUNT,0) END " \
                  " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_CODE = '%s' " \
                  "AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" % (
                      data[1], data[2], data[3], data[4], data[0])
            sqlList.append(sql)

            sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
                  "SET SYSTEM_MONEY =  ifnull(SC_UNLOAD_AMOUNT,0)," \
                  "DIFF_MONEY =   ifnull(ACTUAL_MONEY,0)  - ifnull(SC_UNLOAD_AMOUNT,0)" \
                  " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_CODE = '%s' AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND  DATA_SOURCE = 2 AND AUDIT_STATUS = 0" \
                  % (data[1], data[2], data[3], data[4], data[0])
            sqlList.append(sql)

        mysql.executeAll(sqlList)

    def updateFinalForType2314(self, typeName, typeID):

        sqlList = []

        #
        sql = "SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall),SUM(sale_txnamtall),SUM(supplement_txnamtall)" \
              "FROM subway.%s_fare_statistics  " \
              "WHERE equipmenttype = 2 AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s " \
              "GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid" % (
                  typeName, self.bookdate)
        fc = mysql.select(sql)
        if fc is None:
            return
        for data in fc:
            sql = "UPDATE subway.SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE	" \
                  "SET  %s_INVEST_AMOUNT = ifnull(%s_INVEST_AMOUNT,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND FIND_IN_SET('%s',BOM_CODE)  AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND EQUIPMENT_NUMBER = '%s' AND AUDIT_STATE = 0" \
                  % (typeName, typeName, data[5], data[1], data[2], data[3], data[0], data[4])
            sqlList.append(sql)
            sql = "UPDATE subway.SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE	" \
                  "SET  %s_SELL_AMOUNT = ifnull(%s_SELL_AMOUNT,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND FIND_IN_SET('%s',BOM_CODE)  AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND EQUIPMENT_NUMBER = '%s'  AND AUDIT_STATE = 0" \
                  % (typeName, typeName, data[6], data[1], data[2], data[3], data[0], data[4])
            sqlList.append(sql)
            sql = "UPDATE subway.SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE	" \
                  "SET  %s_FARETK_AMOUNT = ifnull(%s_FARETK_AMOUNT,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND FIND_IN_SET('%s',BOM_CODE)  AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND EQUIPMENT_NUMBER = '%s'  AND AUDIT_STATE = 0" \
                  % (typeName, typeName, data[7], data[1], data[2], data[3], data[0], data[4])
            sqlList.append(sql)

            sql = "UPDATE subway.SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE	" \
                  "SET  DIFFERENCE_AMOUNT =  PAID_AMOUNT -  MARKET_TICKET_AMOUNT - PASSENGER_WORK_AMOUNT - (ifnull(%s_INVEST_AMOUNT,0) + ifnull(%s_SELL_AMOUNT,0) + ifnull(%s_FARETK_AMOUNT,0))," \
                  "SYS_AMOUNT = ifnull(%s_INVEST_AMOUNT,0) + ifnull(%s_SELL_AMOUNT,0) + ifnull(%s_FARETK_AMOUNT,0)" \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND  FIND_IN_SET('%s',BOM_CODE) AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND EQUIPMENT_NUMBER = '%s' AND SYS_DATA_SOURCE = %s AND AUDIT_STATE = 0" \
                  % (typeName, typeName, typeName, typeName, typeName, typeName, data[1], data[2], data[3], data[0],
                     data[4], typeID)
            sqlList.append(sql)

        mysql.executeAll(sqlList)

    def updateFinalForType2315(self, tb1Name, tb2Name, typeStr,bookdate):
        sqlList = []
        global mysql
        gol.set_value('dbName', "subway")

        sql = "SELECT DISTINCT * from" \
              " (SELECT bookdate,lineid,station_number,station_equipment_number,paymethod,posoperid,SUM(recharge_txnamtall) AS sc_rt,SUM(sale_txnamtall) AS sc_st,SUM(supplement_txnamtall) AS sc_sut" \
              " FROM %s" \
              " WHERE    bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid,paymethod) AS t1" \
              " RIGHT  JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,paymethod,posoperid,SUM(recharge_txnamtall) AS lc_rt,SUM(sale_txnamtall) AS lc_st,SUM(supplement_txnamtall) AS lc_sut" \
              " FROM %s" \
              " WHERE   bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid,paymethod) AS t2 " \
              " on t1.bookdate=t2.bookdate AND t1.lineid=t2.lineid AND t1.station_number = t2.station_number" \
              " AND t1.station_equipment_number = t2.station_equipment_number AND  t1.posoperid = t2.posoperid and t1.paymethod = t2.paymethod" \
              " UNION  " \
              " SELECT * from" \
              " (SELECT bookdate,lineid,station_number,station_equipment_number,paymethod,posoperid,SUM(recharge_txnamtall) AS sc_rt,SUM(sale_txnamtall) AS sc_st,SUM(supplement_txnamtall) AS sc_sut" \
              " FROM %s " \
              " WHERE    bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid,paymethod) AS t1" \
              " LEFT JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,paymethod,posoperid,SUM(recharge_txnamtall) AS lc_rt,SUM(sale_txnamtall) AS lc_st,SUM(supplement_txnamtall) AS lc_sut" \
              " FROM %s" \
              " WHERE   bookdate = %s " \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid,paymethod) AS t2 " \
              " on t1.bookdate=t2.bookdate AND t1.lineid=t2.lineid AND t1.station_number = t2.station_number " \
              " AND t1.station_equipment_number = t2.station_equipment_number AND  t1.posoperid = t2.posoperid and t1.paymethod = t2.paymethod" % (
                  tb1Name, bookdate, tb2Name, bookdate, tb1Name, bookdate, tb2Name, bookdate)
        fc = mysql.select(sql)
        if fc is None:
            return
        for data in fc:
            if data[16] != data[7] or data[15] != data[6] or data[17] != data[8]:
                a = 0
                if data[1] == None:
                    a = 9
                print(tb1Name + "对比"+ tb2Name +"有=差异↓↓↓↓↓↓↓"+str(data[a+1])+"号线" + str(data[a+ 2])+"--站点")
                print(data)
            else:
                print(tb1Name + "对比"+ tb2Name +"无=差异↓↓↓↓↓↓↓" + str(data[1]) + "号线" + str(data[2]) + "--站点")
                print(data)

    def updateFinalForType239(self, typeStr, typeid1, typeid2):

        # --结账单据与SC接口差异
        sql = "insert into SW_AU_ITVM_ONLINE_AMOUNT_HIERARCHY_DIFFERENCE(RELATION_SOURCE_ID,LINE_CODE,STATION_CODE," \
              "SETTLE_TIME,TVM_ORDER,TYPE,DEPOSIT_AMOUNT,SALE_AMOUNT,ERROR_REFUND_AMOUNT,COMPARE_TYPE,BE_DEPOSIT_AMOUNT," \
              "BE_SALE_AMOUNT,BE_ERROR_REFUND_AMOUNT,ASSIST_STATUS,REPLACE_STATUS,DATA_TYPE,CREATOR_ID,CREATE_TIME) " \
              "select a.id,a.LINE_CODE ,a.STATION_CODE,date_format('%s','%%Y-%%m-%%d'),a.TVM_CODE,%d," \
              "b.RECHARGE_MONEY * 100 ,b.SELL_TICKET_MONEY * 100,b.FAULT_REFUND_AMOUNT * 100,%d,sc.recharge_txnamtall,sc.sale_txnamtall,sc.refund_txnamtall,1,0,1,0,now() from sw_st_tvm_daily_bill a,sw_st_tvm_daily_bill_electronic_pay b," \
              "(select lineid,station_number,station_equipment_number,sum(sale_txnamtall) sale_txnamtall,sum(refund_txnamtall) refund_txnamtall,sum(recharge_txnamtall) recharge_txnamtall " \
              "from %s_fare_statistics where equipmenttype = '1' and paymethod in ('12','13','14','15') and bookdate = '%s' " \
              "GROUP BY lineid,station_number,station_equipment_number) sc where a.LINE_CODE = sc.lineid and a.STATION_CODE = sc.station_number and a.TVM_CODE = sc.station_equipment_number " \
              "and (date_add(date_format('%s','%%Y-%%m-%%d'), interval 12 hour) " \
              "BETWEEN b.START_TIME and  b.END_TIME) and a.APPROVAL_STATUS ='1' and a.DEL_FLAG= '0' and a.ID = b.RELATION_ID " \
              "and (ifnull(b.RECHARGE_MONEY,0)*100<>sc.recharge_txnamtall or ifnull(b.SELL_TICKET_MONEY,0)*100<>sc.sale_txnamtall or ifnull(b.FAULT_REFUND_AMOUNT,0)*100<>sc.refund_txnamtall);" \
              % (self.bookdate, typeid1, typeid2, typeStr, self.bookdate, self.bookdate)
        mysql.executeAll([sql])

    def accTXTtoSql(self, path):
        gol.set_value('dbName', "subway")
        for root, ds, fs in os.walk(path):
            if root == path:
                for f in fs:
                    if f.find(self.bookdate + ".dat") >= 0:
                        try:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                content = fileRead.readline()

                                if content == '' or content == "\n":
                                    break
                                listV = content.replace('\n', '').split(",")
                                tmpStr = ""
                                for v in listV:
                                    tmpStr = tmpStr + "\'" + v + "\'" + ","
                                sql = "INSERT INTO subway.%s VALUES (\'%s\',%s)" % (f.split("_")[0], f, tmpStr[:-1])
                                mysql.execute(sql)

                            fileRead.close()
                            mysql.commit()
                        except Exception as e:
                            self.log.error("erro" + f + str(e))

    def txt_to_sql(self, listV, f):
        tmpStr = ""
        for v in listV:
            tmpStr = tmpStr + "\'" + v + "\'" + ","
        sql = "INSERT INTO subway.%s VALUES (\'%s\',%s)" % (f.split("_")[0], f, tmpStr[:-1])
        mysql.execute(sql)

    def accFaretoSql(self, path):
        gol.set_value('dbName', "subway")
        for root, ds, fs in os.walk(path):
            if root == path:
                for f in fs:
                    if f.find(self.bookdate + ".dat") >= 0:

                        if f.find("ONETPASS") >= 0:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                    if listV is None or len(listV) != 11 or int(listV[0], 16) > 2:
                                        continue
                                    mType = int(listV[3][0:2], 16)
                                    subType = int(listV[3][2:4], 16)
                                    sql = ""

                                    dictc[str(mType) + "==" + str(subType)] = "1"
                                    deviceId = listV[1][6:8]
                                    lineID = int(listV[1][2:4])
                                    if listV[0] == "01":
                                        if lineID == 1 or lineID == 4:
                                            deviceId = "V%03d" % (int(listV[1][6:8], 16) - 60)
                                        else:
                                            deviceId = "V%03d" % (int(listV[1][6:8], 16))
                                    if listV[0] == "02":

                                        if lineID == 1 or lineID == 4:
                                            deviceId = "BOM%03d" % (int(listV[1][6:8], 16) - 90)
                                        else:
                                            deviceId = "BOM%03d" % (int(listV[1][6:8], 16))

                                    if (mType == 8 and subType == 15) or (mType == 3 and subType == 3) or (
                                            mType == 3 and subType == 2) \
                                            or (mType == 3 and subType == 1) or (mType == 3 and subType == 121):
                                        sql = "INSERT INTO subway.acc_fare_statistics VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d)" % (
                                                  listV[10], int(listV[1][2:4]), listV[1][4:6], listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0, 0, 0, 0, 0)

                                    if (mType == 8 and subType == 14) or (mType == 3 and subType == 12) or (
                                            mType == 3 and subType == 11) \
                                            or (mType == 3 and subType == 10):
                                        sql = "INSERT INTO subway.acc_fare_statistics VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d)" % (
                                                  listV[10], int(listV[1][2:4]), listV[1][4:6], listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0, 0, 0, 0, 0, decimal.Decimal(listV[7]), decimal.Decimal(listV[8]))

                                    if (mType == 33 and subType == 17) or (mType == 34 and subType == 17) or (
                                            mType == 35 and subType == 17) \
                                            or (mType == 3 and subType == 120) or (mType == 3 and subType == 119) or (
                                            mType == 3 and subType == 118):
                                        sql = "INSERT INTO subway.acc_fare_statistics VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d)" % (
                                                  listV[10], int(listV[1][2:4]), listV[1][4:6], listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0,decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0, 0, 0)

                                    if (mType == 3 and subType == 28) or (mType == 3 and subType == 27) or (
                                            mType == 3 and subType == 26) \
                                            or (mType == 1 and subType == 80) or (mType == 1 and subType == 81):
                                        sql = "INSERT INTO subway.acc_fare_statistics VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d)" % (
                                                  listV[10], int(listV[1][2:4]), listV[1][4:6], listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0, 0, 0, decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0)

                                    if sql != "":
                                        mysql.execute(sql)

                                    sql = "INSERT INTO subway.SW_CK_ACC_LINE_M_TRANS_STS (LINE_CODE,TransType,productType," \
                                          "productSubType,PamentMethod,transactionNumber,transactionValue,dataDate) VALUES (" \
                                          "%d, '%s', '%s', '%s', '%s', %d, %d, '%s')" \
                                          % (lineID, listV[3], listV[5], listV[6], listV[9],
                                             decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), listV[10])
                                    mysql.execute(sql)

                                except Exception as err:
                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        elif f.find("ONECPASS") >= 0:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    if listV is None or len(listV) != 10 or int(listV[0][0:2], 16) > 2:
                                        continue
                                    self.txt_to_sql(listV, f)
                                    mType = int(listV[2][0:2], 16)
                                    subType = int(listV[2][2:4], 16)
                                    dictc[str(mType) + "==" + str(subType)] = "1"
                                    sql = ""
                                    deviceId = listV[0][6:8]
                                    lineID = int(listV[0][2:4])
                                    if listV[0][0:2] == "01":
                                        if lineID == 1 or lineID == 4:
                                            deviceId = "V%03d" % (int(listV[0][6:8], 16) - 60)
                                        else:
                                            deviceId = "V%03d" % (int(listV[0][6:8], 16))
                                    elif listV[0][0:2] == "02":

                                        if lineID == 1 or lineID == 4:
                                            deviceId = "BOM%03d" % (int(listV[0][6:8], 16) - 90)
                                        else:
                                            deviceId = "BOM%03d" % (int(listV[0][6:8], 16))

                                    if (mType == 8 and subType == 15) or (mType == 3 and subType == 3) or (
                                            mType == 3 and subType == 2) \
                                            or (mType == 3 and subType == 1) or (mType == 3 and subType == 121):
                                        sql = "INSERT INTO subway.acc_fare_statistics VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d)" % (
                                                  listV[9], int(listV[0][2:4]), listV[0][4:6], listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], decimal.Decimal(listV[6]),decimal.Decimal(listV[7]), 0, 0, 0, 0, 0, 0)

                                    if (mType == 8 and subType == 14) or (mType == 3 and subType == 12) or (
                                            mType == 3 and subType == 11) \
                                            or (mType == 3 and subType == 10):
                                        sql = "INSERT INTO subway.acc_fare_statistics VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d)" % (
                                                  listV[9], int(listV[0][2:4]), listV[0][4:6], listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0, 0, 0, 0, 0, decimal.Decimal(listV[6]), decimal.Decimal(listV[7]))

                                    if (mType == 33 and subType == 17) or (mType == 34 and subType == 17) or (
                                            mType == 35 and subType == 17) \
                                            or (mType == 3 and subType == 120) or (mType == 3 and subType == 119) or (
                                            mType == 3 and subType == 118):
                                        sql = "INSERT INTO subway.acc_fare_statistics VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d)" % (
                                                  listV[9], int(listV[0][2:4]), listV[0][4:6], listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0, decimal.Decimal(listV[6]), decimal.Decimal(listV[7]), 0, 0, 0, 0)

                                    if (mType == 3 and subType == 28) or (mType == 3 and subType == 27) or (
                                            mType == 3 and subType == 26) \
                                            or (mType == 1 and subType == 80) or (mType == 1 and subType == 81):
                                        sql = "INSERT INTO subway.acc_fare_statistics VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d)" % (
                                                  listV[9], int(listV[0][2:4]), listV[0][4:6], listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0, 0, 0, decimal.Decimal(listV[6]),decimal.Decimal(listV[7]), 0, 0)

                                    if sql != "":
                                        mysql.execute(sql)

                                    sql = "INSERT INTO subway.SW_CK_ACC_LINE_T_TRANS_STS (LINE_CODE,TransType,productType," \
                                          "productSubType,PamentMethod,transactionNumber,transactionValue,dataDate) VALUES (" \
                                          "%d, '%s', '%s', '%s', '%s', '%s', %d, '%s')" \
                                          % (lineID, listV[2], listV[4], listV[5], listV[8],
                                             decimal.Decimal(listV[6]), decimal.Decimal(listV[7]), listV[9])
                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        elif f.find("OFFSET") >= 0:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                    if listV is None or len(listV) != 7:
                                        continue
                                    lineID = int(listV[1])
                                    stID = int(listV[2])
                                    transactionStatus = listV[3]
                                    AMOUNT = decimal.Decimal(listV[4])
                                    times = decimal.Decimal(listV[5])
                                    bookdate = listV[6]

                                    sql = "INSERT INTO subway.SW_CK_ACC_LINE_OFFSET (LINE_CODE,transactionStatus,AMOUNT," \
                                          "TIMES,dataDate) VALUES (" \
                                          "%d, '%s', %d, %d, '%s')" \
                                          % (lineID, transactionStatus, AMOUNT, times, bookdate)

                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        elif f.find("TCONSCLEAN") >= 0:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                    if listV is None or len(listV) != 8:
                                        continue
                                    productIssuerId = listV[0]
                                    CLEANTYPE = listV[1]
                                    SERVICEID = int(listV[2])
                                    LINECODE = int(listV[3])
                                    CleanDate = listV[4]
                                    CleanNumber = decimal.Decimal(listV[5])
                                    CleanAmount = decimal.Decimal(listV[6])
                                    bookdate = listV[7]

                                    if 41 <= SERVICEID <= 48:
                                        SERVICEID = 1

                                    sql = "INSERT INTO subway.SW_CK_SYS_CLEANING_CONS_TYPE (SERVICE_ORG,CLEAN_TYPE,TRADE_DATE,LINE_CODE," \
                                          "MONEY,productIssuerId) VALUES (" \
                                          "'%d', '%s','%s',%d, %d,%s)" \
                                          % (SERVICEID, CLEANTYPE, bookdate, LINECODE, CleanAmount, productIssuerId)

                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        elif f.find("PCONSCLEAN") >= 0:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                    if listV is None or len(listV) != 9:
                                        continue
                                    productIssuerId = listV[0]
                                    SERVICEID = int(listV[1])
                                    LINE_CODE = int(listV[2])
                                    productType = listV[3][0:2]
                                    productSubType = listV[3][2:4]
                                    CleanDate = listV[4]
                                    CleanNumber = decimal.Decimal(listV[5])
                                    CleanAmount = decimal.Decimal(listV[6])
                                    TRADTYPE = listV[7]
                                    bookdate = listV[8]

                                    if 41 <= SERVICEID <= 48:
                                        SERVICEID = 1

                                    sql = "INSERT INTO subway.SW_CK_SYS_CLEANING_CONS_TICKET (SERVICE_ORG,CLEAN_TYPE,TRADE_DATE,LINE_CODE," \
                                          "MONEY,productType,productSubType) VALUES (" \
                                          "'%d', '%s','%s',%d, %d,'%s',%s)" \
                                          % (SERVICEID, TRADTYPE, bookdate, LINE_CODE, CleanAmount, productType, productSubType)

                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()
                        elif f.find("SALESCLEAN") >= 0:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                    if listV is None or len(listV) != 9:
                                        continue

                                    SERVICEID = listV[0]
                                    LINE_CODE = int(listV[1])
                                    productType = listV[2]
                                    CleanDate = listV[3]
                                    bili = float(listV[4])
                                    CleanNumber = decimal.Decimal(listV[5])
                                    CleanAmount = decimal.Decimal(listV[6])
                                    paymentMethod = listV[7]
                                    bookdate = listV[8]

                                    sql = "INSERT INTO subway.SW_CK_SYS_CLEANING_SALE_TICKET (SERVICE_ORG,productType,TRADE_DATE,LINE_CODE," \
                                          "MONEY,paymentMethod) VALUES (" \
                                          "'%s', '%s', '%s',%d, %d,'%s')" \
                                          % (SERVICEID, productType, bookdate, LINE_CODE, CleanAmount, paymentMethod)

                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()
                        else:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                except Exception as err:
                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        mysql.commit()

                    tmpDir = self.cf.get("option", "tmpDir")
                    isExists = os.path.exists(tmpDir + self.bookdate)
                    if not isExists:
                        os.makedirs(tmpDir + self.bookdate)
                    shutil.copy(root + f, tmpDir + self.bookdate)
                    os.remove(root + f)
        self.log.info(str(dictc))

    def box5109ToSubway(self):

        ##1/4号线
        ##小于次日0点的装入，放到当天
        sql = "insert into subway.sc_tvm_coin_opt_swich (bookdate,lineid,station_number,station_equipment_number,coin_number,opt_type,amount)" \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),LPAD(station_number,2,0)," \
              " concat('V',LPAD(equipment_number-60,3,0)),conv(right(cash_box_id,8), 16, 10 )," \
              " case operation_type when 1 then 2 when 2 then 1 end,sum(operation_amount) coins_number" \
              " from transaction_datasc.cash_drawer_replacement" \
              " where equipment_type = 1 and operation_type = 2 and right(replacement_time,14) <='%s2359' " \
              " group by line_number,station_number,equipment_number,cash_box_id,operation_type;" % (
                  self.bookdate, self.bookdate)
        mysql.execute(sql)

        nextDay = (datetime.datetime.strptime(self.bookdate, "%Y%m%d") + datetime.timedelta(days=+1)).strftime("%Y%m%d")
        sql = "insert into subway.sc_tvm_coin_opt_swich (bookdate,lineid,station_number,station_equipment_number,coin_number,opt_type,amount)" \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),LPAD(station_number,2,0)," \
              "concat('V',LPAD(equipment_number-60,3,0)),conv(right(cash_box_id,8), 16, 10 ),1,sum(operation_amount) coins_number" \
              " from transaction_datasc.cash_drawer_replacement" \
              " where equipment_type = 1 and operation_type = 2 and right(replacement_time,14) >'%s2359' " \
              " group by line_number,station_number,equipment_number,cash_box_id,operation_type;" \
              % (nextDay, self.bookdate)
        mysql.execute(sql)

        sql = "insert into subway.sc_tvm_coin_opt_swich (bookdate,lineid,station_number,station_equipment_number,coin_number,opt_type,amount)" \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),LPAD(station_number,2,0)," \
              "concat('V',LPAD(equipment_number-60,3,0)),conv(right(cash_box_id,8), 16, 10 )," \
              " case operation_type when 1 then 2 when 2 then 1 end,sum(operation_amount) coins_number" \
              " from transaction_datasc.cash_drawer_replacement " \
              " where equipment_type = 1 and operation_type = 1 " \
              " group by line_number,station_number,equipment_number,cash_box_id,operation_type;" \
              % (self.bookdate)
        mysql.execute(sql)

        ##2/3号线
        ##小于次日0点的装入，放到当天
        sql = "insert into subway.sc_tvm_coin_opt_swich (bookdate,lineid,station_number,station_equipment_number,coin_number,opt_type,amount)" \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),right(station_number,2),concat('V',LPAD(equipment_number,3,0))," \
              "case when line_number = 3 then CONCAT(SUBSTRING(right(money_box_id,8),1,4),LPAD(conv(right(money_box_id,4), 16, 10 ), 4, 0)) else  right(money_box_id,8) end ,change_operation_type,sum(coins_number)*100 coins_number" \
              " from transaction_datasc.cash_box_replacement where equipment_type = 1 and change_operation_type = 1 " \
              " and right(business_data_generation_time,14) <='%s2359' " \
              "group by line_number,station_number,equipment_number,money_box_id,change_operation_type;" \
              % (self.bookdate, self.bookdate)
        mysql.execute(sql)

        nextDay = (datetime.datetime.strptime(self.bookdate, "%Y%m%d") + datetime.timedelta(days=+1)).strftime("%Y%m%d")
        sql = "insert into subway.sc_tvm_coin_opt_swich " \
              "(bookdate,lineid,station_number,station_equipment_number,coin_number,opt_type,amount) " \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),right(station_number,2),concat('V',LPAD(equipment_number,3,0))," \
              "case when line_number = 3 then CONCAT(SUBSTRING(right(money_box_id,8),1,4),LPAD(conv(right(money_box_id,4), 16, 10 ), 4, 0)) else  right(money_box_id,8) end ,change_operation_type,sum(coins_number)*100 coins_number " \
              " from transaction_datasc.cash_box_replacement " \
              " where equipment_type = 1 and change_operation_type = 1 and right(business_data_generation_time,14) >'%s2359' " \
              " group by line_number,station_number,equipment_number,money_box_id,change_operation_type;" \
              % (nextDay, self.bookdate)
        mysql.execute(sql)

        sql = "insert into subway.sc_tvm_coin_opt_swich(bookdate,lineid,station_number,station_equipment_number,coin_number,opt_type,amount) " \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),right(station_number,2),concat('V',LPAD(equipment_number,3,0))," \
              "case when line_number = 3 then CONCAT(SUBSTRING(right(money_box_id,8),1,4),LPAD(conv(right(money_box_id,4), 16, 10 ), 4, 0)) else  right(money_box_id,8) end ,change_operation_type,sum(coins_number)*100 coins_number " \
              " from transaction_datasc.cash_box_replacement" \
              " where equipment_type = 1 and change_operation_type = 2 " \
              " group by line_number,station_number,equipment_number,money_box_id,change_operation_type;" \
              % (self.bookdate)
        mysql.execute(sql)

        mysql.commit()

        sql = "select * from subway.sc_tvm_coin_opt_swich"
        fc = mysql.select(sql)

        sqlList = []
        if fc is None:
            return

        for data in fc:
            sql = "select bookdate,CAST(trim(replace(lineid, '\t', '')) AS SIGNED),station_number,station_equipment_number ,coin_number , opt_type  from subway.sc_tvm_coin_opt where bookdate = '%s' and lineid = '%s' and station_number = '%s'" \
                  " and station_equipment_number = '%s' and coin_number = '%s' and opt_type = %s" \
                  % (data[0], data[1], data[2], data[3], data[4], data[5])
            fc = mysql.select(sql)
            if (fc is None) or len(fc) <= 0:
                sql = "INSERT INTO subway.sc_tvm_coin_opt VALUES ('%s','%s','%s','%s','%s','%s',%d,0)" \
                      % (data[0], data[1], data[2], data[3], data[4], data[5], data[6])
                sqlList.append(sql)
            else:
                sql = "UPDATE subway.sc_tvm_coin_opt " \
                      " SET  amount = amount + %d  " \
                      " WHERE  bookdate = '%s' and lineid = '%s' and station_number = '%s'" \
                      " and station_equipment_number = '%s' and coin_number = '%s' and opt_type = %s" \
                      % (data[6], data[0], data[1], data[2], data[3], data[4], data[5])
                sqlList.append(sql)
        mysql.executeAll(sqlList)

        mysql.executeAll(["truncate table subway.sc_tvm_coin_opt_swich"])

    def mainMethod(self):

        # 清空昨日50+数据库数据
        removeLastDayFromSql()
        self.log.info("清空昨日50+数据库数据成功")

        # 解析
        self.anlysisData()
        self.log.info("解析数据成功")

        # acc 数据入库 入 subway 库
        sourceRoot = self.cf.get("option", "source_file")
        self.accFaretoSql(sourceRoot + "acc/")
        self.log.info("acc 数据入库成功")

        # 归类统计 入tmp
        gol.set_value('dbName', "transaction_datasc")
        self.statisticsToSql()
        gol.set_value('dbName', "transaction_datalc")
        self.statisticsToSql()
        self.log.info("归类统计成功")

        # 2次归类统计 入subway fare库
        gol.set_value('dbName', "transaction_datasc")
        self.finalStatistics("sc_fare_statistics")
        gol.set_value('dbName', "transaction_datalc")
        self.finalStatistics("lc_fare_statistics")
        self.log.info("2次归类统计数据成功")
        #
        # 出战入库
        self.sc_out_statistics()
        self.update_day_cell()
        self.log.info("日报表统计数据成功")
        #
        # # 数据比较差异入库
        self.updataToFinal()
        self.log.info("数据比较差异成功")
        # #
        # 清空源文件夹文件
        sourceRoot = self.cf.get("option", "source_file")
        del_file(sourceRoot)
        self.log.info("清空源文件夹文件成功")


if __name__ == "__main__":
    dt = DownLoadTools()
    dt.updateFinalForType2315("sc_fare_statistics", "lc_fare_statistics", "1", "20210108")
	#dt.updateFinalForType2315("sc_fare_statistics", "acc_fare_statistics", "1", "20210102")
    #dt.updateFinalForType2315("lc_fare_statistics", "acc_fare_statistics", "2", "20210102")


